Les bases

CREATE INDEX [ name ] ON table_name
  ( { ( column_name [, ...] ) | ( expression ) }
  [ ASC | DESC ]
  [ NULLS { FIRST | LAST } ]) 

DROP INDEX name
  [ CASCADE | RESTRICT ] 

Quelques exemples

CREATE INDEX test1_id_index ON test1 (id)

DROP INDEX title_idx

Quelques exemples

CREATE INDEX test1_id_index ON test1 (id)

SELECT id FROM test1 WHERE id=5 ✅

SELECT id FROM test1 ❌

SELECT id FROM test1 WHERE fact(id)=120 ❌

Combiner différents index

SELECT * FROM mytable WHERE a = 5 AND b = 6

1. Scan de l'index sur a
2. Scan de l'index sur b
3. Ajout d'un AND sur les résultats

Attention à ORDER BY

Index sur plusieurs colonnes

CREATE INDEX ON mytable (a,b);

SELECT * FROM mytable WHERE a = 5 AND b = 6 ✅

SELECT * FROM mytable WHERE a = 5 ✅

SELECT * FROM mytable WHERE b = 6 ❌

Index sur des expressions

CREATE INDEX ON  mytable (lower(a))

SELECT * FROM mytable WHERE lower(a) = 'value' ✅

SELECT * FROM mytable WHERE a = upper('value') ❌

SELECT * FROM mytable WHERE a = 'value' ❌

Index partiels

CREATE INDEX ON  mytable (a) WHERE a IS NULL

SELECT * FROM mytable WHERE a IS NULL ✅

SELECT * FROM mytable WHERE a = 5 ❌

Index couvrants

CREATE INDEX ON  mytable (a) INCLUDE (b)

SELECT a,b FROM mytable WHERE a = 5 ✅

SELECT a,b FROM mytable WHERE a = 5 AND b = 6 ❌

Les types d'index

Les types d'index

Les index des extensions

• Bloom
• Vodka
• Rum
• Hierarchical Navigable Small Worlds (HNSW)

Les types d'index

CREATE INDEX name ON table (column ) USING method

CREATE INDEX pointloc ON points USING gist (box(location,location))

CREATE INDEX gin_idx ON documents_table USING GIN (locations)

Les classes d'opérateur

CREATE INDEX name ON table (column opclass [ ( opclass_options ) ] )

• text_pattern_ops
• varchar_pattern_ops
• bpchar_pattern_ops

Utilisé pour la recherche de pattern (régular expressions, sous-chaînes de caractères...)

Tri

CREATE INDEX name ON table (column)
  [ COLLATE collation ]
  [ ASC | DESC ]
  [ NULLS { FIRST | LAST } ]

• Le tri dépend de la collation
• Choisissez l'ordre de votre choix
• Choisissez ce que vous voulez faire des valeurs NULL

NULL values

CREATE INDEX name ON table (column) [ NULLS [ NOT ] DISTINCT ] 

Using an index

• Dépendant des statistiques
• On ne peut pas le forcer
• Arrêtez d'être des control freaks!

Explain plans

EXPLAIN [ ( option [, ...] ) ] statement
where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    WAL [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

Explain plans

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)

Usage

\d pg_stat_all_indexes
                   View "pg_catalog.pg_stat_all_indexes"
    Column     |           Type           | Collation | Nullable | Default 
---------------+--------------------------+-----------+----------+---------
 relid         | oid                      |           |          | 
 indexrelid    | oid                      |           |          | 
 schemaname    | name                     |           |          | 
 relname       | name                     |           |          | 
 indexrelname  | name                     |           |          | 
 idx_scan      | bigint                   |           |          | 
 last_idx_scan | timestamp with time zone |           |          | 
 idx_tup_read  | bigint                   |           |          | 
 idx_tup_fetch | bigint                   |           |          | 

Attention

• Index en doublon
• Index inutilisés
• Index manquants

Some scripts

Use the index, Luke!